/** 
 * @Title: ExcelReader.java 
 * @Package com.zizo.excel 
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月17日 下午8:34:47 
 */
package com.zizo.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @ClassName: ExcelReader
 * @Description: 读Excel文件的工具类，使用的是apache的POI框架
 * @author HeYan
 * @date 2017年1月17日 下午8:34:47
 * 
 */
public class ExcelReader {
	@SuppressWarnings("unused")
	private String readFilePath;// Excel 的文件路径
	private Workbook wb;

	public Workbook getWb() {
		return wb;
	}

	public void setWb(Workbook wb) {
		this.wb = wb;
	}

	public ExcelReader(String readFilePath) {
		super();
		this.readFilePath = readFilePath;
		try {
			if (readFilePath.endsWith("xls")) {
				wb = new HSSFWorkbook(new FileInputStream(new File(readFilePath)));
			} else if (readFilePath.endsWith("xlsx")) {
				wb = new XSSFWorkbook(new FileInputStream(new File(readFilePath)));
			} else {
				System.err.println("The excel filename must be *.xls[x],so Re-Type. <(^-^)>");
				System.exit(1);
			}
		} catch (FileNotFoundException e) {
			System.err.println("The file[" + readFilePath + "] is not found!\n");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Title: getFirstSheet
	 * @Description: 得到第一个Sheet
	 * @return
	 * @return Sheet
	 * @author Alexis
	 */
	public Sheet getFirstSheet() {
		Sheet firstSheet = wb.getSheetAt(0);
		return firstSheet;
	}

	/**
	 * @Title: getSheetList
	 * @Description: 得到当前Excel表格所有的Sheet
	 * @return
	 * @return List<Sheet>
	 * @author Alexis
	 */
	public List<Sheet> getSheetList() {
		List<Sheet> sheetList = new ArrayList<>();
		Iterator<Sheet> it = wb.iterator();
		while (it.hasNext()) {
			Sheet st = it.next();
			sheetList.add(st);
		}
		return sheetList;
	}

	/**
	 * @Title: getExcelTitle
	 * @Description: 得到Excel给定Sheet的Title
	 * @param sheetIndex
	 * @return
	 * @return String[]
	 * @author Alexis
	 */
	public String[] getExcelTitle(Sheet sheet) {
		Row row = sheet.getRow(sheet.getFirstRowNum());
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getStringCellValue(row.getCell((short) i));
		}
		return title;
	}

	/**
	 * @Title: getExcelContent
	 * @Description: 得到Excel 给定的Sheet的Content(Title以外)
	 * @param sheetIndex
	 * @return
	 * @return List<Row>
	 * @author Alexis
	 */
	public List<Row> getExcelContent(Sheet sheet, boolean hasHead) {
		List<Row> rowList = new ArrayList<>();
		int start = sheet.getFirstRowNum();
		int end = sheet.getLastRowNum();
		int index = hasHead ? start + 1 : start;
		for (int i = index; i < end + 1; i++) {
			Row row = sheet.getRow(i);
			rowList.add(row);
		}
		return rowList;
	}

	@SuppressWarnings("deprecation")
	public static String getStringCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		String strCell = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

}
